import sqlite3
import sys
import traceback

import messagebox

conn = sqlite3.connect('../database.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

device_table_index = {}
data_cache = {}
process_file_count = 0
process_first = False


def commit_data_cache(i):
    global data_cache
    print('start commit, gate_pass_info_{} finish'.format(i))
    for table_name in data_cache.keys():
        sql = "insert into {}(device, gate_id, car_type, card_type, pass_timestamp, location, card_tail, secret_card) values(?, ?, ?, ?, ?, ?, ?, ?)".format(table_name)
        cursor.executemany(sql, data_cache[table_name])
    conn.commit()
    data_cache = {}


if __name__ == '__main__':
    print('select gate_pass_info_device_index')
    sql = 'select * from gate_pass_info_device_index'
    result = conn.execute(sql).fetchall()
    for row in result:
        data = tuple(row)
        device = data[0]
        index = data[1]
        device_table_index[device] = index
    print('finish select gate_pass_info_device_index')

    try:
        for i in range(160, 1553):
            table_name = 'gate_pass_info_{}'.format(i)
            sql = 'select * from {}'.format(table_name)
            result = [tuple(row) for row in conn.execute(sql).fetchall()]
            for data in result:
                if data[0] not in device_table_index:
                    continue
                index = device_table_index[data[0]]
                table_name = 'gate_pass_info_device_{}'.format(index)
                if table_name not in data_cache:
                    data_cache[table_name] = []
                data_cache[table_name].append(data)
            commit_data_cache(i)
        conn.close()
    except Exception as e:
        messagebox.showinfo("提示", "发生错误")
        print("insert exception")
        traceback.print_exc()
        sys.exit(-1)